package com.lab.idea.bl.util;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.lab.idea.bl.entity.*;
import com.lab.idea.bl.vo.QueryData;
import com.lab.idea.common.util.LabJsonUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.*;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.math.BigDecimal;
import java.util.*;

/**
 * @author mao
 */
@Slf4j
public class BlExcelReadUtil {

    public static String CELL_STYPE = "cellStyle";

    public static void main(String[] args) {
        try {
          /*  QueryData queryData = new QueryData().setColumns(CollUtil.newArrayList(new ExcelColumn().setColumnTitle("测试"),new ExcelColumn().setColumnTitle("变色")));
            queryData.setDatas(CollUtil.newArrayList());
            queryData.getDatas().add(new JSONObject().set("测试","测试").set("变色","变色"));
            queryData.getDatas().add(new JSONObject().set("测试","ddd").set("变色",222));
            queryData.getDatas().add(new JSONObject().set("测试","ddd").set("变色",222).set(CELL_STYPE,new JSONObject().set("变色",new JSONObject().set("bgColor","red"))));

            ExcelOut excelOut = new ExcelOut().setOutName("测试变色");
            write(queryData,excelOut,"/Users/mao/workspace/laibo/bl-tool/2023-11-09/test.xlsx");*/

            ExcelTemplate template = new ExcelTemplate().setSheets(new ArrayList<>());
            template.setExcelPath("/Users/mao/workspace/laibo/bl-tool/D:/质量控制/质控数据库/11.09测试三普质控样品登记及结果评价表(交换性相对误差，单位不换算）.xlsm");

            template.getSheets().add(new ExcelSheet().setSheetName("三普样品检测数据信息台账（化学指标）").setColumnRow(2));
            List<ExcelSheet> excelSheets = readSheet(template);
            log.info("sdf");
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static List<ExcelSheet> readSheet(ExcelTemplate excelTemplate) {
        List<ExcelSheet> excelSheets = new ArrayList<>();
        List<Sheet> sheets = loadSheets(excelTemplate);
        for (int sheetIndex = 0; sheetIndex < sheets.size(); sheetIndex++) {
            Sheet sheet = sheets.get(sheetIndex);
            ExcelSheet excelSheet = null;
            if (!CollUtil.isEmpty(excelTemplate.getSheets())) {
                for (ExcelSheet excelTemplateSheet : excelTemplate.getSheets()) {
                    if (StrUtil.equals(excelTemplateSheet.getSheetName(),sheet.getSheetName())) {
                        excelSheet = excelTemplateSheet;break;
                    }
                }
            }
            if (excelSheet == null) {
                excelSheet = new ExcelSheet().setExcelId(excelTemplate.getExcelId()).setSheetIndex(sheetIndex).setSheetName(sheet.getSheetName()).setColumnRow(0).setColumns(CollUtil.newArrayList());
                excelSheet.setSheetId(IdWorker.getIdStr());
            }
            excelSheet.setColumns(CollUtil.newArrayList());
            for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                if (rowIndex > excelSheet.getColumnRow()) {
                    break;
                }
                Row row = sheet.getRow(rowIndex);
                if (row == null || row.getFirstCellNum() < 0) {
                    continue;
                }
                for (int colIndex = row.getFirstCellNum(); colIndex <= row.getLastCellNum(); colIndex++) {
                    Cell cell = row.getCell(colIndex);
                    String val = getCellValue(cell);
                    String prop = String.format("c%s",colIndex + 1);
                    boolean addColumn = true;
                    if (!CollUtil.isEmpty(excelSheet.getColumns())) {
                        for (ExcelColumn column : excelSheet.getColumns()) {
                            if (StrUtil.equals(prop,column.getColumnProp())) {
                                addColumn = false;
                                if (!StrUtil.isBlankIfStr(val)) {
                                    column.setColumnTitle(val);
                                    break;
                                }
                            }
                        }
                    }
                    if (addColumn && !StrUtil.isEmptyIfStr(val)) {
                        ExcelColumn excelColumn = new ExcelColumn().setExcelId(excelSheet.getExcelId()).setSheetId(excelSheet.getSheetId())
                                .setColumnTitle(val).setColumnProp(prop).setSheetName(sheet.getSheetName()).setColumnOrder(colIndex);
                        excelSheet.getColumns().add(excelColumn);
                    }
                }
            }
            excelSheets.add(excelSheet);
        }
        return excelSheets;
    }

    private static List<Sheet> loadSheets(ExcelTemplate excelTemplate){
        ExcelReader excelReader = ExcelUtil.getReader(FileUtil.getInputStream(excelTemplate.getExcelPath()));
        return excelReader.getSheets();
    }

    /**
     * 查询关联数据
     * @param excelTemplate     模板
     * @param joinMap           关联
     * @return  key: 关联属性，key: 关联值，value: 分组值
     */
    public static Map<String,Map<String,List<JSONObject>>> readJoin(ExcelTemplate excelTemplate, Map<String,ExcelJoin> joinMap) {
        Map<String,Map<String,List<JSONObject>>> resultMap = new HashMap<>();
        if (CollUtil.isEmpty(joinMap)) {
            return resultMap;
        }
        List<Sheet> sheets = loadSheets(excelTemplate);
        for (int sheetIndex = 0; sheetIndex < sheets.size(); sheetIndex++) {
            Sheet sheet = sheets.get(sheetIndex);
            if (!joinMap.containsKey(sheet.getSheetName())) {
                continue;
            }
            ExcelJoin excelJoin = joinMap.get(sheet.getSheetName());
            if (CollUtil.isEmpty(excelJoin.getColumns())) {
                continue;
            }
            if (!resultMap.containsKey(excelJoin.getJoinProp())) {
                resultMap.put(excelJoin.getJoinProp(),new HashMap<>());
            }
            Map<String,ExcelColumn> columnMap = new HashMap<>();
            for (ExcelColumn column : excelJoin.getColumns()) {
                columnMap.put(column.getColumnProp(),column);
            }
            Map<String, List<JSONObject>> rowMap = resultMap.get(excelJoin.getJoinProp());
            ExcelSheet excelSheet = excelJoin.getSheet();
            for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                if (rowIndex <= excelSheet.getColumnRow()) {
                    continue;
                }
                Row row = sheet.getRow(rowIndex);
                if (row == null || row.getFirstCellNum() < 0) {
                    continue;
                }
                JSONObject rowData = new JSONObject();
                for (int colIndex = row.getFirstCellNum(); colIndex <= row.getLastCellNum(); colIndex++) {
                    Cell cell = row.getCell(colIndex);
                    String val = getCellValue(cell);
                    String prop = String.format("c%s",colIndex + 1);
                    rowData.set(prop,val);
                    if (columnMap.containsKey(prop)) {
                        rowData.set(columnMap.get(prop).getColumnTitle(),val);
                    }
                }
                String keyVal = LabJsonUtil.formatCode(excelJoin.toFormat(excelJoin.getMatchColumn()), rowData);
                if (rowMap.containsKey(keyVal)) {
                    rowMap.get(keyVal).add(rowData);
                } else {
                    List<JSONObject> list = new ArrayList<>();
                    list.add(rowData);
                    rowMap.put(keyVal,list);
                }
            }
        }
        return resultMap;
    }

    private static String getCellValue(Cell cell) {
        if (cell == null){
            return StrUtil.EMPTY;
        }
        String ret = StrUtil.EMPTY;
        switch (cell.getCellType()) {
            case BLANK:
                ret = "";
                break;
            case BOOLEAN:
                ret = cell.getBooleanCellValue() ? "是" : "否";
                break;
            case ERROR:
                break;
            case FORMULA:
                Workbook wb = cell.getSheet().getWorkbook();
                CreationHelper crateHelper = wb.getCreationHelper();
                FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
                ret = getCellValue(evaluator.evaluateInCell(cell));
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date theDate = cell.getDateCellValue();
                    if (theDate != null) {
                        ret = cn.hutool.core.date.DateUtil.format(theDate, "yyyy-MM-dd");
                    }
                } else if (cell.getCellStyle() != null && cell.getCellStyle().getDataFormatString().contains("%")) {
                    String s = cell.getNumericCellValue() * 100 + "%";
                    ret = StrUtil.replace(s, ".0%","%");
                }else {
                    ret = NumberToTextConverter.toText(cell.getNumericCellValue());
                }
                break;
            case STRING:
                ret = cell.getRichStringCellValue().getString();
                break;
            default:
        }
        // 有必要自行trim
        return ret;
    }

    public static Map<String,List<JSONObject>> readExcelMap(byte[] bytes){
        ExcelReader excelReader = ExcelUtil.getReader(new ByteArrayInputStream(bytes));
        List<Sheet> sheets = excelReader.getSheets();
        Map<String,List<JSONObject>> map = new HashMap<>();
        if (CollUtil.isEmpty(sheets)) {
            return map;
        }
        for (int i = 0; i < sheets.size(); i++) {
            Sheet sheet = sheets.get(i);
            List<JSONObject> list = readExcelSheet(sheet);
            map.put(sheet.getSheetName(),list);
            map.put(i+StrUtil.EMPTY,list);
        }
        return map;
    }

    /**
     * 读取excel - sheet数据
     *  会识别表头，以表头名为属性返回
     * @param sheet 指定sheet
     * @return      sheet 行列数据
     */
    public static List<JSONObject> readExcelSheet(Sheet sheet) {
        List<JSONObject> list = new ArrayList<>();
        JSONObject titleMap = new JSONObject();
        for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null || row.getFirstCellNum() < 0) {
                continue;
            }
            JSONObject rowData = new JSONObject();
            boolean fillHeader = false;
            boolean haveValue = false;
            for (int colIndex = row.getFirstCellNum(); colIndex <= row.getLastCellNum(); colIndex++) {
                Cell cell = row.getCell(colIndex);
                String val = getCellValue(cell);
                String prop = String.format("c%s",colIndex + 1);
                if (!haveValue && !StrUtil.isBlankIfStr(val)) {
                    haveValue = true;
                }
                if (!titleMap.containsKey(prop) || (!StrUtil.isBlankIfStr(val) && StrUtil.isBlankIfStr(titleMap.get(prop)))) {
                    titleMap.set(prop,val);
                    fillHeader = true;
                } else {
                    rowData.set(prop,val);
                }
            }
            if (!rowData.isEmpty() && haveValue) {
                if (fillHeader) {
                    for (Map.Entry<String, Object> entry : rowData.entrySet()) {
                        Object value = entry.getValue();
                        if (StrUtil.isBlankIfStr(value)) {
                            continue;
                        }
                        titleMap.set(entry.getKey(),value);
                    }
                } else {
                    for (Map.Entry<String, Object> entry : titleMap.entrySet()) {
                        if (StrUtil.isBlankIfStr(entry.getValue())) {
                            continue;
                        }
                        String title = entry.getValue().toString();
                        if (StrUtil.contains(title, "（")) {
                            title = StrUtil.subBefore(title, "（",true);
                        }
                        rowData.set(title,rowData.getStr(entry.getKey(),StrUtil.EMPTY));
                    }
                    list.add(rowData);
                }
            }
        }
        return list;
    }

    public static List<JSONObject> readExcel(byte[] bytes,int sheetIndex) {
        ExcelReader excelReader = ExcelUtil.getReader(new ByteArrayInputStream(bytes));
        List<Sheet> sheets = excelReader.getSheets();
        if (CollUtil.isEmpty(sheets) || sheetIndex > sheets.size()) {
            return new ArrayList<>();
        }
        if (sheetIndex < 0 ) {
            sheetIndex = 0;
        }
        return readExcelSheet(sheets.get(sheetIndex));
    }

    public static void write(QueryData queryData, String sheetName, String path) {
        try {
            XSSFWorkbook workbook;
            if (FileUtil.isFile(path)) {
                workbook = new XSSFWorkbook(path);
            } else {
                workbook = new XSSFWorkbook(XSSFWorkbookType.XLSM);
            }
            XSSFSheet sheet = workbook.getSheet(sheetName);
            if (sheet == null) {
                sheet = workbook.createSheet(sheetName);
                XSSFRow sheetRow = sheet.createRow(0);
                for (ExcelColumn column : queryData.getColumns()) {
                    XSSFCell sheetCell = sheetRow.createCell(sheetRow.getLastCellNum() == -1 ? 0 : sheetRow.getLastCellNum());
                    sheetCell.setCellStyle(workbook.createCellStyle());
                    sheetCell.setCellValue(column.getColumnTitle());
                }
                queryData.setStartIndex(1);
            }
            for (int i = 0; i < queryData.getDatas().size(); i++) {
                JSONObject rowData = queryData.getDatas().get(i);
                XSSFRow sheetRow = sheet.createRow(queryData.getStartIndex() + i + 1);
                for (ExcelColumn column : queryData.getColumns()) {
                    XSSFCell sheetCell = sheetRow.createCell(sheetRow.getLastCellNum() == -1 ? 0 : sheetRow.getLastCellNum());
                    if (rowData.containsKey(CELL_STYPE)) {
                        JSONObject styleObj = rowData.getJSONObject(CELL_STYPE);
                        if (styleObj != null && styleObj.size() > 0 && styleObj.containsKey(column.getColumnTitle())) {
                            JSONObject titleStyle = styleObj.getJSONObject(column.getColumnTitle());
                            XSSFCellStyle cellStyle = workbook.createCellStyle();
                            for (Map.Entry<String, Object> entry : titleStyle.entrySet()) {
                                switch (entry.getKey()) {
                                    case "backgroundColor":
                                    case "bgColor":
                                        IndexedColors[] values = IndexedColors.values();
                                        String color = entry.getValue().toString();
                                        for (IndexedColors indexedColor : values) {
                                            if (StrUtil.equalsIgnoreCase(indexedColor.name(),color)) {
                                                cellStyle.setFillForegroundColor(indexedColor.getIndex());
                                                cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                                                break;
                                            }
                                        }
                                        break;
                                    default:
                                        break;
                                }
                            }
                            sheetCell.setCellStyle(cellStyle);
                        }
                    }
                    Object value = rowData.get(column.getColumnTitle());
                    if (value instanceof BigDecimal) {
                        value = ((BigDecimal) value).toPlainString();
                    } else if (value == null) {
                        value = StrUtil.EMPTY;
                    }
                    sheetCell.setCellValue(value.toString());
                }
            }
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);
            FileUtil.writeBytes(outputStream.toByteArray(),path);
            workbook.close();
        } catch (Exception e) {
            if (StrUtil.equals("OpenXML4JRuntimeException",e.getClass().getSimpleName())) {
                log.warn("excel数据兼容保存");
            } else {
                log.error("excel数据写入失败");
            }
        }
    }
}
